package za.ac.wits.elen7045.persistence.dao;

import java.util.Date;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import za.ac.wits.elen7045.persistence.Environment;
import za.ac.wits.elen7045.persistence.datasource.DataSource;
import za.ac.wits.elen7045.persistence.datasource.DataSourceFactory;
import za.ac.wits.elen7045.persistence.exception.DaoException;
import za.ac.wits.elen7045.persistence.exception.DataSourceProcessException;
import za.ac.wits.elen7045.persistence.model.Account;
import za.ac.wits.elen7045.persistence.model.AccountType;
import za.ac.wits.elen7045.persistence.model.CreditCardProviderStatement;
import za.ac.wits.elen7045.persistence.model.MunicipalityStatement;
import za.ac.wits.elen7045.persistence.model.Statement;
import za.ac.wits.elen7045.persistence.model.TelcoServiceProviderStatement;
import za.ac.wits.elen7045.persistence.util.SQLQueries;

public class SqlStatementDAO implements SqlDao {

	private DataSource<Statement> statementDSource;
	private DataSource<MunicipalityStatement> muniStatementDSource;
	private DataSource<CreditCardProviderStatement> ccProviderStatementDSource;
	private DataSource<TelcoServiceProviderStatement> telcoServProviderStatementDSource;
	
	/** 
	 * Constructor allows package access ONLY to force the use of the factory 
	 * */
	SqlStatementDAO(Environment env) {
		statementDSource = DataSourceFactory.getDataSource(Statement.class, env);
		muniStatementDSource = DataSourceFactory.getDataSource(MunicipalityStatement.class, env);
		ccProviderStatementDSource = DataSourceFactory.getDataSource(CreditCardProviderStatement.class, env);
		telcoServProviderStatementDSource = DataSourceFactory.getDataSource(TelcoServiceProviderStatement.class, env);
	}
	
	public Statement getLatestAccountStatement(Account account) {
		try {
			statementDSource.open();
			String query = SQLQueries.getLatestAccountStatement(account);
			Iterator<Statement> iterator = statementDSource.retrieve(query).iterator();
			if (!iterator.hasNext()) {
				return null;
			}
			return getTypeSpecificStatement(iterator.next());
		} catch (Exception e) {
			throw new DaoException("Unable to load the latest statement for account (number="+ account.getAccountNumber() +") from database!");
		} 
		finally {
			statementDSource.close();
		}
	}
	
	public Statement getAccountStatement(Account account, Statement statement) {
		try {
			statementDSource.open();
			String query = SQLQueries.getAccountStatement(account, statement);
			Iterator<Statement> iterator = statementDSource.retrieve(query).iterator();
			if (!iterator.hasNext()) {
				return null;
			}
			return getTypeSpecificStatement(iterator.next());
		} catch (Exception e) {
			throw new DaoException("Unable to load statement!");
		} 
		finally {
			statementDSource.close();
		}
	}
	
	public List<Statement> getAccountStatements(Account account, Date date) {
		try {
			statementDSource.open();
			String query = SQLQueries.getAccountStatements(account, date);
			Map<Integer, Statement> statements = new LinkedHashMap<Integer, Statement>();
			/* get statements from source */
			for (Statement statement : statementDSource.retrieve(query)) {
				statements.put(statement.getId(), statement);
			}
			/* Get statements for an account: 
			 * NOTE: 
			 * 		By making another call to source inside the above for-loop may result in 
			 * 		some database crashes and are therefore written as separated actions... 
			 */
			for (Integer statementId : statements.keySet()) {
				statements.put(statementId, getTypeSpecificStatement(statements.get(statementId)));
			}
			return new LinkedList<Statement>(statements.values());
		} catch (Exception e) {
			throw new DaoException("Unable to load statement for date '"+ date.toString() +"'");
		} 
		finally {
			statementDSource.close();
		}
	}
	
	public List<Statement> getAccountStatementsBefore(Account account, Date date) {
		try {
			statementDSource.open();
			String query = SQLQueries.getAccountStatementsBefore(account, date);
			Map<Integer, Statement> statements = new LinkedHashMap<Integer, Statement>();
			/* get statements from source */
			for (Statement statement : statementDSource.retrieve(query)) {
				statements.put(statement.getId(), statement);
			}
			/* Get statements for an account: 
			 * NOTE: 
			 * 		By making another call to source inside the above for-loop may result in 
			 * 		some database crashes and are therefore written as separated actions... 
			 */
			for (Integer statementId : statements.keySet()) {
				statements.put(statementId, getTypeSpecificStatement(statements.get(statementId)));
			}
			return new LinkedList<Statement>(statements.values());
		} catch (Exception e) {
			throw new DaoException("Unable to load statements before '"+ date.toString() +"'");
		} 
		finally {
			statementDSource.close();
		}
	}
	
	public List<Statement> getAccountStatementsAfter(Account account, Date date) {
		try {
			statementDSource.open();
			String query = SQLQueries.getAccountStatementsAfter(account, date);
			Map<Integer, Statement> statements = new LinkedHashMap<Integer, Statement>();
			/* get statements from source */
			for (Statement statement : statementDSource.retrieve(query)) {
				statements.put(statement.getId(), statement);
			}
			/* Get statements for an account: 
			 * NOTE: 
			 * 		By making another call to source inside the above for-loop may result in 
			 * 		some database crashes and are therefore written as separated actions... 
			 */
			for (Integer statementId : statements.keySet()) {
				statements.put(statementId, getTypeSpecificStatement(statements.get(statementId)));
			}
			return new LinkedList<Statement>(statements.values());
		} catch (Exception e) {
			throw new DaoException("Unable to load statements after '"+ date.toString() +"'");
		} 
		finally {
			statementDSource.close();
		}
	}
	
	public List<Statement> getAccountStatements(Account account) {
		try {
			statementDSource.open();
			String query = SQLQueries.getAccountStatements(account);
			Map<Integer, Statement> statements = new LinkedHashMap<Integer, Statement>();
			/* get statements from source */
			for (Statement statement : statementDSource.retrieve(query)) {
				statements.put(statement.getId(), statement);
			}
			/* Get statements for an account: 
			 * NOTE: 
			 * 		By making another call to source inside the above for-loop may result in 
			 * 		some database crashes and are therefore written as separated actions... 
			 */
			for (Integer statementId : statements.keySet()) {
				statements.put(statementId, getTypeSpecificStatement(statements.get(statementId)));
			}
			return new LinkedList<Statement>(statements.values());
		} catch (Exception e) {
			throw new DaoException("Unable to load statements for account (account number ="+ account.getAccountNumber() +")");
		} 
		finally {
			statementDSource.close();
		}
	}
	
	public boolean statementExist(Account account, Statement statement) {
		try {
			statementDSource.open();
			String query = SQLQueries.statementExist(statement);
			Iterator<Statement> iterator = statementDSource.retrieve(query).iterator();
			AccountType accountType = account.getServiceProviderType().getAccountType();
			if (iterator.hasNext()) {
				if (accountType != AccountType.OTHER) {
					statement = iterator.next();
					if (accountType == AccountType.MUNICIPALITY) {
						query = SQLQueries.municipalityStatementExist(statement);
					} else if (accountType == AccountType.CREDIT_CARD_PROVIDER) {
						query = SQLQueries.creditCardProviderStatementExist(statement);
					} else if (accountType == AccountType.TELCO_SERVICE_PROVIDER) {
						query = SQLQueries.telcoServiceProviderStatementExist(statement);
					}
					return statementDSource.retrieve(query).iterator().hasNext();
				} else {
					return true;
				}
			}
			return false;
		} catch (Exception e) {
			throw new DataSourceProcessException("Unable to verify accounts from database!");
		} 
		finally {
			statementDSource.close();
		}
	}
	
	public void addAccountStatement(Account account, Statement statement) {
		if (statement.getAccountId() == 0) {
			statement.setAccountId(account.getId());
		}
		try {
			statementDSource.open();
			/* Step 1 - add new Statement */
			statementDSource.submit(SQLQueries.addAccountStatement(statement));
			statementDSource.commit();
			/* Step 2 - get "updated" copy of statement (with ID) */
			Statement statementTemp = getAccountStatement(account, statement);
			statement.setId(statementTemp.getId());
			/* Step 3 - add type-specific statements */
			addTypeSpecificStatement(statement);
		} catch (Exception e) {
			throw new DaoException("Unable to load statements for account (account number ="+ account.getAccountNumber() +")");
		} 
		finally {
			statementDSource.close();
		}
	}
	
	public void removeAccountStatement(Account account, Statement statement) {
		if (statement.getAccountId() == 0) {
			statement.setAccountId(account.getId());
		}
		try {
			statementDSource.open();
			/* Step 1 - remove Statement */
			statementDSource.submit(SQLQueries.removeAccountStatement(statement));
			statementDSource.commit();
			/* Step 3 - remove type-specific statements */
			removeTypeSpecificStatement(statement);
		} catch (Exception e) {
			throw new DaoException("Unable to load statements for account (account number ="+ account.getAccountNumber() +")");
		} 
		finally {
			statementDSource.close();
		}
	}
	
	public void addAccountStatements(Account account, List<Statement> statements) {
		try {
			statementDSource.open();
			for (Statement statement : statements) {
				if (statement.getAccountId() == 0) {
					statement.setAccountId(account.getId());
				}
				/* Step 1 - add new Statement */
				statementDSource.submit(SQLQueries.addAccountStatement(statement));
				/* Step 2 - get "updated" copy of statement (with ID) */
				statement = statementDSource.retrieve(SQLQueries.getAccountStatement(account, statement)).iterator().next();
				/* Step 3 - add type-specific statements */
				addTypeSpecificStatement(statement);
				statementDSource.commit();
			}
		} catch (Exception e) {
			throw new DaoException("Unable to load statements for account (account number ="+ account.getAccountNumber() +")");
		} 
		finally {
			statementDSource.close();
		}
	}
	
	public void removeAccountStatements(Account account, List<Statement> statements) {
		try {
			statementDSource.open();
			for (Statement statement : statements) {
				if (statement.getAccountId() == 0) {
					statement.setAccountId(account.getId());
				}
				/* Step 1 - remove Statement */
				statementDSource.submit(SQLQueries.removeAccountStatement(statement));
				statementDSource.commit();
				/* Step 3 - remove type-specific statements */
				removeTypeSpecificStatement(statement);
			}
		} catch (Exception e) {
			throw new DaoException("Unable to load statements for account (account number ="+ account.getAccountNumber() +")");
		} 
		finally {
			statementDSource.close();
		}
	}
	
	/* Municipality Statement */
	
	private MunicipalityStatement getMunicipalityStatement(Statement statement) {
		try {
			muniStatementDSource.open();
			String query = SQLQueries.getMunicipalityStatement(statement);
			Iterator<MunicipalityStatement> iterator = muniStatementDSource.retrieve(query).iterator();
			if (!iterator.hasNext()) {
				return null;
			}
			return iterator.next();
		} catch (Exception e) {
			throw new DaoException("Unable to load municipality statement (statement number ="+ statement.getStatementNumber() +") from database!");
		} 
		finally {
			muniStatementDSource.close();
		}
	}
	
	private void addMunicipalityStatement(MunicipalityStatement statement) {
		try {
			muniStatementDSource.open();
			muniStatementDSource.submit(SQLQueries.addMunicipalityStatement(statement));
			muniStatementDSource.commit();
		} catch (Exception e) {
			throw new DaoException("Unable to add municipality statement (statement number ="+ statement.getStatementNumber() +") from database!");
		} 
		finally {
			muniStatementDSource.close();
		}
	}
	
	private void removeMunicipalityStatement(MunicipalityStatement statement) {
		try {
			muniStatementDSource.open();
			muniStatementDSource.submit(SQLQueries.removeMunicipalityStatement(statement));
			muniStatementDSource.commit();
		} catch (Exception e) {
			throw new DaoException("Unable to remove municipality statement (statement number ="+ statement.getStatementNumber() +") from database!");
		} 
		finally {
			muniStatementDSource.close();
		}
	}
	
	/* Credit Card Provider Statement */
	
	private CreditCardProviderStatement getCreditCardProviderStatement(Statement statement) {
		try {
			ccProviderStatementDSource.open();
			String query = SQLQueries.getCreditCardProviderStatement(statement);
			Iterator<CreditCardProviderStatement> iterator = ccProviderStatementDSource.retrieve(query).iterator();
			return iterator.hasNext() ? iterator.next() : null;
		} catch (Exception e) {
			throw new DaoException("Unable to load credit-card-provider statement (statement number ="+ statement.getStatementNumber() +") from database!");
		} 
		finally {
			ccProviderStatementDSource.close();
		}
	}
	
	private void addCreditCardProviderStatement(CreditCardProviderStatement statement) {
		try {
			ccProviderStatementDSource.open();
			ccProviderStatementDSource.submit(SQLQueries.addCreditCardProviderStatement(statement));
			ccProviderStatementDSource.commit();
		} catch (Exception e) {
			throw new DaoException("Unable to add credit-card-provider statement (statement number ="+ statement.getStatementNumber() +") from database!");
		} 
		finally {
			ccProviderStatementDSource.close();
		}
	}
	
	private void removeCreditCardProviderStatement(CreditCardProviderStatement statement) {
		try {
			ccProviderStatementDSource.open();
			ccProviderStatementDSource.submit(SQLQueries.removeCreditCardProviderStatement(statement));
			ccProviderStatementDSource.commit();
		} catch (Exception e) {
			throw new DaoException("Unable to remove credit-card-provider statement (statement number ="+ statement.getStatementNumber() +") from database!");
		} 
		finally {
			ccProviderStatementDSource.close();
		}
	}
	
	/* TELCO Service Provider Statement */
	
	private TelcoServiceProviderStatement getTelcoServiceProviderStatement(Statement statement) {
		try {
			telcoServProviderStatementDSource.open();
			String query = SQLQueries.getTelcoServiceProviderStatement(statement);
			Iterator<TelcoServiceProviderStatement> iterator = telcoServProviderStatementDSource.retrieve(query).iterator();
			return iterator.hasNext() ? iterator.next() : null;
		} catch (Exception e) {
			throw new DaoException("Unable to load telco-serivce-provider statement (statement number ="+ statement.getStatementNumber() +") from database!");
		} 
		finally {
			telcoServProviderStatementDSource.close();
		}
	}
	
	private void addTelcoServiceProviderStatement(TelcoServiceProviderStatement statement) {
		try {
			telcoServProviderStatementDSource.open();
			telcoServProviderStatementDSource.submit(SQLQueries.addTelcoServiceProviderStatement(statement));
			telcoServProviderStatementDSource.commit();
		} catch (Exception e) {
			throw new DaoException("Unable to add telco-serivce-provider statement (statement number ="+ statement.getStatementNumber() +") from database!");
		} 
		finally {
			telcoServProviderStatementDSource.close();
		}
	}
	
	private void removeTelcoServiceProviderStatement(TelcoServiceProviderStatement statement) {
		try {
			telcoServProviderStatementDSource.open();
			telcoServProviderStatementDSource.submit(SQLQueries.removeTelcoServiceProviderStatement(statement));
			telcoServProviderStatementDSource.commit();
		} catch (Exception e) {
			throw new DaoException("Unable to remove telco-serivce-provider statement (statement number ="+ statement.getStatementNumber() +") from database!");
		} 
		finally {
			telcoServProviderStatementDSource.close();
		}
	}
	
	/* Utilities */
	
	private Statement getTypeSpecificStatement(Statement statement) {
		/* IF EXIST: return a MunicipalityStatement */
		MunicipalityStatement municipalityStatement = getMunicipalityStatement(statement);
		if (municipalityStatement != null) {
			return municipalityStatement;
		}
		/* ELSE IF EXIST: return a CreditCardProviderStatement */
		CreditCardProviderStatement creditCardProviderStatement = getCreditCardProviderStatement(statement);
		if (creditCardProviderStatement != null) {
			return creditCardProviderStatement;
		}
		/* ELSE IF EXIST: return a TelcoServiceProviderStatement */
		TelcoServiceProviderStatement telcoServiceProviderStatement = getTelcoServiceProviderStatement(statement);
		if (telcoServiceProviderStatement != null) {
			return telcoServiceProviderStatement;
		}
		/* ELSE: return Statement */
		return statement;
	}
	
	private void addTypeSpecificStatement(Statement statement) {
		/* MunicipalityStatement */
		if (statement instanceof MunicipalityStatement) {
			addMunicipalityStatement((MunicipalityStatement) statement);
		} 
		/* CreditCardProviderStatement */
		else if (statement instanceof CreditCardProviderStatement) {
			addCreditCardProviderStatement((CreditCardProviderStatement) statement);
		} 
		/* TelcoServiceProviderStatement */
		else if (statement instanceof TelcoServiceProviderStatement) {
			addTelcoServiceProviderStatement((TelcoServiceProviderStatement) statement);
		}
	}
	
	private void removeTypeSpecificStatement(Statement statement) {
		/* IF EXIST: add MunicipalityStatement */
		if (statement instanceof MunicipalityStatement) {
			removeMunicipalityStatement((MunicipalityStatement) statement);
		} else if (statement instanceof CreditCardProviderStatement) {
			removeCreditCardProviderStatement((CreditCardProviderStatement) statement);
		} else if (statement instanceof TelcoServiceProviderStatement) {
			removeTelcoServiceProviderStatement((TelcoServiceProviderStatement) statement);
		}
	}
	
}
